package com.huya.k12.translate.dao.impl;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.huya.k12.translate.dao.WordDao;
import com.huya.k12.translate.model.entity.Word;
import com.huya.k12.translate.model.mapper.WordRowMapper;
import com.huya.k12.translate.model.query.WordQuery;
import com.huya.k12.translate.model.vo.PageControl;
import com.huya.k12.translate.model.vo.WordVo;

@Repository
public class WordDaoImpl implements WordDao{
	
	private Logger logger = Logger.getLogger(WordDaoImpl.class);
	
	private static String encode = "utf-8";
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Word get(Integer Id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Word> listAll() {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM word WHERE isDelete = 0 ");
		List<Word>  results = jdbcTemplate.query(sb.toString(),new WordRowMapper());
		return results;
	}

	@Override
	public Integer countData(WordQuery query) {
		// TODO Auto-generated method stub
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT count(1) as count  FROM word ");
		sb.append(" WHERE isDelete = 0 ");
		Integer state = query.getState();
		if(state != null)
			sb.append(" AND state = ").append(state);
		Integer status = query.getStatus();
		if(status != null )
			sb.append(" AND status = ").append(status);
		String wordId = query.getWordId();
		if(StringUtils.isNotBlank(wordId))
			sb.append(" AND wordId like ").append("'%").append(wordId).append("%'");
		List<Integer> results = jdbcTemplate.query(
				sb.toString(), 
				new RowMapper<Integer>(){
					@Override
					public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
						// TODO Auto-generated method stub
						return rs.getInt("count");
					}
				});
		return results==null?null:(results.size()>0?results.get(0):null);
	}

	@Override
	public List<Word> listByQuery(WordQuery query, PageControl pageControl) {
		// TODO Auto-generated method stub
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM  word ");
		sb.append(" WHERE isDelete = 0 ");
		Integer status = query.getStatus();
		if(status != null)
			sb.append(" AND status = ").append(status);
		
		String wordId = query.getWordId();
		if(StringUtils.isNotBlank(wordId))
			sb.append(" AND wordId like ").append("'%").append(wordId).append("%'");
		
		String orderBy = query.getOrderBy();
		if(StringUtils.isNotBlank(orderBy))
			sb.append(" ORDER BY ").append(orderBy);
			
		sb.append(" LIMIT ? , ? ");
		
		
		List<Word> results = jdbcTemplate.query(
				sb.toString(), new WordRowMapper(), pageControl.getStart(), pageControl.getSize());
		return results;
	}

	@Override
	public boolean init(final List<Word> wordList) {
		// TODO Auto-generated method stub 
		StringBuilder sb = new StringBuilder();
		sb.append(" INSERT INTO word (wordId, fileName, en, ar, de, es, fr, hi, india, it, ja, ko,"
				+ "  ms, pt, ru, th, tl, tr, ur_rPK, vi, zh, zh_rCN, zh_rSG, createTime)");
		sb.append(" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		int[]result = jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, wordList.get(i).getWordId());
				ps.setString(2, wordList.get(i).getFileName());
				ps.setString(3, strEncode(wordList.get(i).getEn()));
				ps.setString(4, strEncode(wordList.get(i).getAr()));
				ps.setString(5, strEncode(wordList.get(i).getDe()));
				ps.setString(6, strEncode(wordList.get(i).getEs()));
				ps.setString(7, strEncode(wordList.get(i).getFr()));
				ps.setString(8, strEncode(wordList.get(i).getHi()));
				ps.setString(9, strEncode(wordList.get(i).getIndia()));
				ps.setString(10, strEncode(wordList.get(i).getIt()));
				ps.setString(11, strEncode(wordList.get(i).getJa()));
				ps.setString(12, strEncode(wordList.get(i).getKo()));
				ps.setString(13, strEncode(wordList.get(i).getMs()));
				ps.setString(14, strEncode(wordList.get(i).getPt()));
				ps.setString(15, strEncode(wordList.get(i).getRu()));
				ps.setString(16, strEncode(wordList.get(i).getTh()));
				ps.setString(17, strEncode(wordList.get(i).getTl()));
				ps.setString(18, strEncode(wordList.get(i).getTr()));
				ps.setString(19, strEncode(wordList.get(i).getUr_rPK()));
				ps.setString(20, strEncode(wordList.get(i).getVi()));
				ps.setString(21, strEncode(wordList.get(i).getZh()));
				ps.setString(22, strEncode(wordList.get(i).getZh_rCN()));
				ps.setString(23, strEncode(wordList.get(i).getZh_rSG()));
				ps.setDate(24, new Date(new java.util.Date().getTime()));
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return wordList.size();
			}
		});
		return result.length>0;
	}

	@Override
	public Word get(String wordId) {
		// TODO Auto-generated method stub
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM word ");
		sb.append(" WHERE isDelete = 0 ");
		sb.append(" AND wordId = ? ");
		List<Word> results = jdbcTemplate.query(sb.toString(),new WordRowMapper(),wordId);
		if(results.size() > 1){
			logger.error("duplicate key : "+ results.get(0).getWordId());
		}
		return results != null ? (results.size()>0 ? results.get(0) : null ) : null;
	}

	@Override
	public boolean update(Word word) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" UPDATE word SET en = ?, zh = ?, zh_rCN = ?, zh_rSG = ?, ar = ?, de = ?, es=?, fr=?, hi=?, india=?, it=?,"
				+ " ja=?, ko=?, ms=?, pt=?, ru=?, th=?, tl=?, tr=?, ur_rPK=?, vi=?, status = ? WHERE wordId = ? ");
		int result = jdbcTemplate.update(sb.toString(), strEncode(word.getEn()), strEncode(word.getZh()), strEncode(word.getZh_rCN()), strEncode(word.getZh_rSG()), 
				strEncode(word.getAr()), strEncode(word.getDe()), strEncode(word.getEs()), strEncode(word.getFr()), strEncode(word.getHi()),strEncode(word.getIndia()), 
				strEncode(word.getIt()),strEncode(word.getJa()),strEncode(word.getKo()),strEncode(word.getMs()),strEncode(word.getPt()),strEncode(word.getRu()),
				strEncode(word.getTh()),strEncode(word.getTl()),strEncode(word.getTr()),strEncode(word.getUr_rPK()),strEncode(word.getVi()),word.getStatus(),word.getWordId());
		return result > 0 ;
	}

	@Override
	public List<Word> listByStatus(Integer status) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT * FROM word ");
		sb.append(" WHERE isDelete = 0 ");
		sb.append(" AND status = ? ");
		List<Word> results = jdbcTemplate.query(sb.toString(), new WordRowMapper(), status);
		return results;
	}

	@Override
	public boolean addWord(final List<Word> list) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" INSERT INTO word( wordId, fileName, en, createTime, state) ");
		sb.append(" VALUES (?,?,?,?,?) ");
		int[] results = jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, list.get(i).getWordId());
				ps.setString(2, list.get(i).getFileName());
				ps.setString(3, strEncode(list.get(i).getEn()));
				ps.setDate(4, new Date(new java.util.Date().getTime()));
				ps.setInt(5, 1);
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return list.size();
			}
		});
		boolean result = true;
		for(int i=0;i<results.length;i++){
			if(results[i]<=0){
				result = false;
				break;
			}
		}
		return result;
	}

	@Override
	public boolean updateWord(final List<Word> list) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" UPDATE word SET en = ?, state= ?, ar = null, de=null, es=null, fr=null, hi=null, india=null, it=null, ja=null,"
				+ " ko=null, ms=null, pt=null, ru=null, th=null, tl=null, tr=null, ur_rPK=null, vi=null, zh=null, zh_rCN=null, zh_rSG=null WHERE wordId = ? ");
		int[] results = jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, strEncode(list.get(i).getEn()));
				ps.setInt(2, 1);
				ps.setString(3, list.get(i).getWordId());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return list.size();
			}
		});
		boolean result = true;
		for(int i=0;i<results.length;i++){
			if(results[i]<=0){
				result = false;
				break;
			}
		}
		return result;
	}

	@Override
	public void updateByCountryCode(final Map<String, String> map, String countryCode) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE word SET status = ? , ");
		if(StringUtils.isBlank(countryCode))
			return ;
		if(countryCode.equals("ar"))
			sb.append(" ar = ? ");
		if(countryCode.equals("de"))
			sb.append(" de = ? ");
		if(countryCode.equals("es"))
			sb.append(" es = ? ");
		if(countryCode.equals("fr"))
			sb.append(" fr = ? ");
		if(countryCode.equals("hi"))
			sb.append(" hi = ? ");
		if(countryCode.equals("in"))
			sb.append(" india = ? ");
		if(countryCode.equals("it"))
			sb.append(" it = ? ");
		if(countryCode.equals("ja"))
			sb.append(" ja = ? ");
		if(countryCode.equals("ko"))
			sb.append(" ko = ? ");
		if(countryCode.equals("ms"))
			sb.append(" ms = ? ");
		if(countryCode.equals("pt"))
			sb.append(" pt = ? ");
		if(countryCode.equals("ru"))
			sb.append(" ru = ? ");
		if(countryCode.equals("th"))
			sb.append(" th = ? ");
		if(countryCode.equals("tl"))
			sb.append(" tl = ? ");
		if(countryCode.equals("tr"))
			sb.append(" tr = ? ");
		if(countryCode.equals("ur-rPK"))
			sb.append(" ur_rPK = ? ");
		if(countryCode.equals("vi"))
			sb.append(" vi = ? ");
		if(countryCode.equals("zh"))
			sb.append(" zh = ? ");
		if(countryCode.equals("zh-rCN"))
			sb.append(" zh_rCN = ? ");
		if(countryCode.equals("zh-rSG"))
			sb.append(" zh_rSG = ? ");
		
		sb.append(" WHERE wordId = ? ");
		
		final Iterator it = map.keySet().iterator();
		
		
	    jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				String key  = (String) it.next();
				ps.setInt(1, 1);
				ps.setString(2, strEncode(map.get(key)));
				ps.setString(3, key);
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return map.size();
			}
		});
	}

	@Override
	public void deleteAll() {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM word");
		jdbcTemplate.update(sb.toString());
		
	}
	
	private String strEncode(String str){
		if(StringUtils.isNotBlank(str))
			try {
				return URLEncoder.encode(str, encode);
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}
		return null;
	}

	@Override
	public void resetStatus() {
		StringBuilder sb = new StringBuilder();
		sb.append(" UPDATE word SET status = 0 ");
		jdbcTemplate.update(sb.toString());
		// TODO Auto-generated method stub
		
	}

	@Override
	public void deleteWord(final List<Word> list) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" UPDATE word SET isDelete = 1 ");
		sb.append(" WHERE wordId = ? ");
		jdbcTemplate.batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, list.get(i).getWordId());
			}
			
			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return list.size();
			}
		});
	}
}
